﻿using System;
using System.Collections;
using System.Data;
using System.Text;
using CRM.UTIL;

namespace CRM.WEB
{
    public partial class export : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            String type = Request["type"];

            switch (type)
            {
                case "excel":
                    //数据来源
                    ArrayList data = GetOrderInfo();

                    String json = Request["columns"];
                    ArrayList columns = (ArrayList)JSON.Decode(json);
                    ExportExcel(columns, data,"订单编号");
                    break;

                default:
                    break;
            }
        }

        public ArrayList GetOrderInfo()
        {
            //查询条件
            String orderNo = Request["OrderNO"] ?? ""; ;
            String guaranteeNO = Request["guaranteeNO"] ?? "";
            String buyerFullName = Request["buyerFullName"] ?? "";
            String orderDate = Request["orderDate"] ?? "";
            String isDel = Request["isDel"] ?? "";

            //字段排序
            String sortField = Request["sortField"];
            String sortOrder = Request["sortOrder"];

            Hashtable result = GetOrderInfo(orderNo, guaranteeNO, buyerFullName, orderDate, isDel, sortField, sortOrder);
            return result["data"] as ArrayList;
        }

        public Hashtable GetOrderInfo(string orderNo, string guaranteeNO, string buyerFullName, string orderDate, string isDel, string sortField, string sortOrder)
        {
            //System.Threading.Thread.Sleep(300);
            StringBuilder sb = new StringBuilder();
            string sql = string.Format(@"SELECT * FROM OrderInfo WHERE 1=1 ");
            sb.Append(sql);
            if (!string.IsNullOrEmpty(orderNo))
            {
                sb.AppendFormat(@"and  orderNo like '%{0}%' ", orderNo);
            }
            if (!string.IsNullOrEmpty(guaranteeNO))
            {
                sb.AppendFormat(@"and  guaranteeNO like '%{0}%' ", guaranteeNO);
            }
            if (!string.IsNullOrEmpty(buyerFullName))
            {
                sb.AppendFormat(@"and  buyerFullName like '%{0}%' ", buyerFullName);
            }
            if (!string.IsNullOrEmpty(orderDate))
            {
                sb.AppendFormat(@"and  CONVERT(varchar(100), orderDate,23) like '%{0}%' ", orderDate);
            }
            if (!string.IsNullOrEmpty(isDel))
            {
                sb.AppendFormat(@"and  isDel = '%{0}%' ", isDel);
            }
            if (String.IsNullOrEmpty(sortField) == false)
            {
                if (sortOrder != "desc") sortOrder = "asc";
                sql += " order by " + sortField + " " + sortOrder;
            }
            else
            {
                sql += " order by orderDate desc";
            }
            DBChannel db = new DBChannel();
            DataTable dt = db.GetDataBySql(sb.ToString());

            ArrayList dataAll = DataTable2ArrayList(dt);
            ArrayList data = new ArrayList();
            for (int i = 0, l = dataAll.Count; i < l; i++)
            {
                Hashtable record = (Hashtable)dataAll[i];
                if (record == null) continue;

                data.Add(record);
            }
            Hashtable result = new Hashtable();
            result["data"] = data as ArrayList;
            result["total"] = dataAll.Count;

            return result;
        }






        private static ArrayList DataTable2ArrayList(DataTable data)
        {
            ArrayList array = new ArrayList();
            for (int i = 0; i < data.Rows.Count; i++)
            {
                DataRow row = data.Rows[i];

                Hashtable record = new Hashtable();
                for (int j = 0; j < data.Columns.Count; j++)
                {
                    object cellValue = row[j];
                    if (cellValue.GetType() == typeof(DBNull))
                    {
                        cellValue = null;
                    }
                    record[data.Columns[j].ColumnName] = cellValue;
                }
                array.Add(record);
            }
            return array;
        }

        public void ExportExcel(ArrayList columns, ArrayList data,string filaName)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "GB2312";
            //Response.Charset = "UTF-8";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + filaName + ".xlsx");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
            Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
            EnableViewState = false;
            Response.Write(ExportTable(data, columns));
            Response.End();
        }

        public static string ExportTable(ArrayList data, ArrayList columns)
        {
            StringBuilder sb = new StringBuilder();
            //data = ds.DataSetName + "\n";
            int count = 0;

            //data += tb.TableName + "\n";
            sb.AppendLine("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">");
            sb.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
            //写出列名
            sb.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;\">");
            foreach (Hashtable column in columns)
            {
                sb.AppendLine("<td>" + column["header"] + "</td>");
            }
            sb.AppendLine("</tr>");

            //写出数据
            foreach (Hashtable row in data)
            {
                sb.Append("<tr>");
                foreach (Hashtable column in columns)
                {
                    if (column["field"] == null) continue;
                    Object value = row[column["field"]];
                    sb.AppendLine("<td>" + value + "</td>");
                }
                sb.AppendLine("</tr>");
                count++;
            }
            sb.AppendLine("</table>");

            return sb.ToString();
        }
    }
}